"""
Case Type   : A兼容性-subtype
Case Name   : package中定义的subtype的基础类型自定义type，将其作为另一个package中函数入参
Create At   : 2025/6/18
Owner       : wan005
Description :
    1.创建对象类型
    2.定义subtype
    3.定义创建包package2
    4.定义创建包package2 body
    5.调用包中的函数
Expect      :
    1.成功
    2.成功
    3.成功
    4.查询到两条数据
    5.查询到两条数据
History     :
"""
import os
import unittest
from yat.test import Node
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class Security(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'---{os.path.basename(__file__)} start---')
        self.userNode = Node(node='PrimaryDbAmode')
        self.sh_primy = CommonSH()
        self.common = Common()
        self.db_name = self.userNode.db_name
        self.pkg1 = 'p1_subtype_009'
        self.pkg2 = 'p2_subtype_009'
        self.type = 'tp_subtype_009'

    def test_security(self):
        text = '----step1:创建对象类型;expect:成功----'
        self.logger.info(text)
        sql_cmd1 = f'create or replace type tp_subtype_009 as ' \
                   f'object(attribute1 varchar2(100),attribute2 number);'
        res1 = self.sh_primy.execut_db_sql(sql_cmd1, dbname=self.db_name)
        self.logger.info(res1)
        self.assertEqual('CREATE TYPE', res1, '执行失败' + text)

        text = '----step2:定义subtype;expect:成功----'
        self.logger.info(text)
        sql_cmd2 = f'''
            create or replace package {self.pkg1} is
            subtype addresstype is {self.type};
            end {self.pkg1};
            '''
        res2 = self.sh_primy.execut_db_sql(sql_cmd2, dbname=self.db_name)
        self.logger.info(res2)
        self.assertEqual('CREATE PACKAGE', res2, '执行失败' + text)

        text = '----step3:定义创建包package2;expect:成功----'
        self.logger.info(text)
        sql_cmd3 = f'''
            create or replace package {self.pkg2} is
            function getemployeedetails(param1 {self.pkg1}.addresstype) 
            return varchar2;
            end {self.pkg2};
            '''
        res3 = self.sh_primy.execut_db_sql(sql_cmd3, dbname=self.db_name)
        self.logger.info(res3)
        self.assertEqual('CREATE PACKAGE', res3, '执行失败' + text)

        text = '----step4:定义创建包package2 body;expect:成功----'
        self.logger.info(text)
        sql_cmd4 = f'''
            create or replace package body {self.pkg2} is
            function getemployeedetails(param1 {self.pkg1}.addresstype) 
            return varchar2 is
            result varchar2(100);
            begin
            result := param1.attribute1 || ' ' || to_char(param1.attribute2);
            return result;
            end;
            end {self.pkg2};
            '''
        res4 = self.sh_primy.execut_db_sql(sql_cmd4, dbname=self.db_name)
        self.logger.info(res4)
        self.assertEqual('CREATE PACKAGE BODY', res4, '执行失败' + text)

        text = '----step5:查询数据;expect:查询到两条数据----'
        self.logger.info(text)
        sql_cmd5 = f'''
            create extension if not exists gms_output;
            declare
            myparam {self.type};
            result varchar2(100);
            begin
            myparam := {self.type}('value1', 123);
            result := {self.pkg2}.getemployeedetails(myparam);
            gms_output.put_line('result is: ' || result);
            end;
            '''
        res5 = self.sh_primy.execut_db_sql(sql_cmd5, dbname=self.db_name)
        self.logger.info(res5)
        self.assertIn('ANONYMOUS BLOCK EXECUTE', res5, '执行失败' + text)

    def tearDown(self):
        text = '----step1:清理环境;expect:成功----'
        self.logger.info(text)
        cle_cmd1 = f'drop package {self.pkg2};'
        cle_res1 = self.sh_primy.execut_db_sql(cle_cmd1, dbname=self.db_name)
        self.logger.info(cle_res1)
        self.assertIn('DROP PACKAGE', cle_res1, text + '执行失败')

        cle_cmd2 = f'drop package {self.pkg1};'
        cle_res2 = self.sh_primy.execut_db_sql(cle_cmd2, dbname=self.db_name)
        self.logger.info(cle_res2)
        self.assertEqual('DROP PACKAGE', cle_res2, text + '执行失败')

        cle_cmd3 = f'drop type {self.type};'
        cle_res3 = self.sh_primy.execut_db_sql(cle_cmd3, dbname=self.db_name)
        self.logger.info(cle_res3)
        self.assertEqual('DROP TYPE', cle_res3, text + '执行失败')
        self.logger.info(f'---{os.path.basename(__file__)} end---')
